﻿using System.Globalization;
using System.IO;
using System.Web.UI.HtmlControls;
using H2.NET.Common;
using H2.NET.Data.Common;
using H2.NET.Web.Management.Config.Service;
using H2.NET.Web.Pages.SMD.Model;
using H2.NET.Web.Workflow.DwzCode;
using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;

namespace H2.NET.Web.Pages.SMD
{
    public partial class StoreDetailReport : BasePage
    {
        private readonly ConfigService _configService = new ConfigService();
        public bool HasValue = false;
        protected void Page_Load(object sender, EventArgs e)
        {
            var action = AksRequest.GetQueryString("action");
            if (action == "save")
            {
                SaveSummary();
                SuccessMsg();
            }
            if (action == "reset")
            {
                var year = Utils.StrToInt(AksRequest.GetFormString("year"), 0);
                (t_smd_Summary._Year == year & t_smd_Summary._ReportType == "detail").Delete<t_smd_Summary>(
                Storage.Context3);
                SummaryData();
            }
            else if (action == "export")
            {
                Export();
            }

            SummaryData();
        }

        protected void SummaryData()
        {
            var year = Utils.StrToInt(AksRequest.GetFormString("year"), 0);
            clsCmm.clsDB.Storage("smdDB");
            if (year == 0)
            {
                year = DateTime.Now.Year;
            }
            yearSearch.Value = year.ToString(CultureInfo.InvariantCulture);

            clsCmm.clsDB.Storage("smdDB");
            var summarys = clsCmm.clsDB.Query("select * from t_smd_Summary where year=" + year + " and ReportType='detail'");
            if (summarys == null || summarys.Rows.Count == 0)
            {
                clsCmm.clsDB.Storage("oleDB");
                var sql = "select * from (select count(1) ddcn_east from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'EAST' and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") a,(select count(1) ddcn_south from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'South' and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") b,( select count(1) ddcn_west from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'West' and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") c,( select count(1) ddcn_north from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'North' and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") d,( select count(1) [ddcn_all] from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") e,( select count(1) dcss_east from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'EAST' and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") f,( select count(1) dcss_south from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'South' and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") g,( select count(1) dcss_west from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'West' and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") h,( select count(1) dcss_north from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'North' and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") i,( select count(1) dcss_all from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") j,(select count(1) ddcn_east_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'EAST' and m.StoreType like '%DDCN%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") a1,(select count(1) ddcn_south_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'South' and m.StoreType like '%DDCN%' and YEAR(SubmitDate) = " + year + ") b1,( select count(1) ddcn_west_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'West' and m.StoreType like '%DDCN%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") c1,( select count(1) ddcn_north_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'North' and m.StoreType like '%DDCN%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") d1,( select count(1) [ddcn_all_r] from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.StoreType like '%DDCN%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") e1,( select count(1) dcss_east_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'EAST' and m.StoreType like '%DCSS%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") f1,( select count(1) dcss_south_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'South' and m.StoreType like '%DCSS%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") g1,( select count(1) dcss_west_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'West' and m.StoreType like '%DCSS%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") h1,( select count(1) dcss_north_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.AreaName = 'North' and m.StoreType like '%DCSS%' and m.IsRenew = 1 and YEAR(SubmitDate) = " + year + ") i1,( select count(1) dcss_all_r from t_StoreMasterData m where m.Process = 'New Store Application' and Step = 'Finish' and m.IsEapprove = 1 and m.IsRenew = 1 and m.StoreType like '%DCSS%' and YEAR(SubmitDate) = " + year + ") j1";
                var dt =
                    clsCmm.clsDB.Query(sql).Rows[0];
                ddcn_East_new_e_f.Value = dt["ddcn_east"].ToString();
                ddcn_South_new_e_f.Value = dt["ddcn_south"].ToString();
                ddcn_West_new_e_f.Value = dt["ddcn_west"].ToString();
                ddcn_North_new_e_f.Value = dt["ddcn_north"].ToString();
                ddcn_All_new_e_f.Value = dt["ddcn_all"].ToString();

                dcss_East_new_e_f.Value = dt["dcss_east"].ToString();
                dcss_South_new_e_f.Value = dt["dcss_south"].ToString();
                dcss_West_new_e_f.Value = dt["dcss_west"].ToString();
                dcss_North_new_e_f.Value = dt["dcss_north"].ToString();
                dcss_All_new_e_f.Value = dt["dcss_all"].ToString();

                ddcn_East_renew_e_f.Value = dt["ddcn_east_r"].ToString();
                ddcn_South_renew_e_f.Value = dt["ddcn_south_r"].ToString();
                ddcn_West_renew_e_f.Value = dt["ddcn_west_r"].ToString();
                ddcn_North_renew_e_f.Value = dt["ddcn_north_r"].ToString();
                ddcn_All_renew_e_f.Value = dt["ddcn_all_r"].ToString();

                dcss_East_renew_e_f.Value = dt["dcss_east_r"].ToString();
                dcss_South_renew_e_f.Value = dt["dcss_south_r"].ToString();
                dcss_West_renew_e_f.Value = dt["dcss_west_r"].ToString();
                dcss_North_renew_e_f.Value = dt["dcss_north_r"].ToString();
                dcss_All_renew_e_f.Value = dt["dcss_all_r"].ToString();
            }
            else
            {
                HasValue = true;
                var columns = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXMXH') order by seq").Tables[0].Rows;
                var rows = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXMXL') order by seq").Tables[0].Rows;
                for (var c = 0; c < columns.Count; c++)
                {
                    for (var r = 0; r < rows.Count; r++)
                    {
                        var contorl = Page.FindControl(rows[r]["name"] + "_" + columns[c]["name"]);
                        if (contorl is HtmlInputText)
                        {
                            var htmlInputText = contorl as HtmlInputText;
                            htmlInputText.Value = summarys.Select("rowName='" + rows[r]["name"] + "' and columnName = '" + columns[c]["name"] + "'")[0]["Value"].ToString();
                        }
                    }
                }
            }
        }

        protected void SaveSummary()
        {
            
            (t_smd_Summary._Year == Utils.StrToInt(yearSearch.Value,DateTime.Now.Year) & t_smd_Summary._ReportType == "detail").Delete<t_smd_Summary>(
                Storage.Context3);

            var summarys = new List<t_smd_Summary>();
            var columns = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXMXH') order by seq").Tables[0].Rows;
            var rows = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXMXL') order by seq").Tables[0].Rows;
            for (var c = 0; c < columns.Count; c++)
            {
                for (var r = 0; r < rows.Count; r++)
                {
                    var summary = new t_smd_Summary();
                    summary.Year = DateTime.Now.Year;
                    summary.RowName = rows[r]["name"].ToString();
                    summary.ColumnName = columns[c]["name"].ToString();
                    summary.ReportType = "detail";
                    summary.Value = Utils.StrToInt(AksRequest.GetFormString(rows[r]["name"] + "_" + columns[c]["name"]), 0);
                    summarys.Add(summary);
                }
            }

// ReSharper disable once CoVariantArrayConversion
            Storage.Save(Storage.Context3, summarys.ToArray());
        }

        protected void Export()
        {
            var ms = new MemoryStream();

            var year = Utils.StrToInt(AksRequest.GetFormString("year"), DateTime.Now.Year);
            clsCmm.clsDB.Storage("smdDB");
            var summarys = clsCmm.clsDB.Query("select * from t_smd_Summary where year=" + year + " and ReportType='detail'");
            using (summarys)
            {
                HSSFWorkbook hssfworkbook;
                using (
                    var file = new FileStream(Server.MapPath("~/") + "Pages\\SMD\\Excel\\NewAndUdateDetail.xls",
                        FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }

                var sheet = hssfworkbook.GetSheetAt(0);

                var columns =
                    _configService.GetList(
                        "ParentId = (select configid from t_smd_Config where code='XKFXMXH') order by seq").Tables[0].Rows;
                var rows =
                    _configService.GetList(
                        "ParentId = (select configid from t_smd_Config where code='XKFXMXL') order by seq").Tables[0].Rows;

                for (var r = 0; r < rows.Count; r++)
                {
                    for (var c = 0; c < columns.Count; c++)
                    {
                        var value = summarys.Select("rowName='" + rows[r]["name"] + "' and columnName = '" +
                                                    columns[c]["name"] + "'")[0]["Value"].ToString();
                        if (sheet.GetRow(2 + r) == null) {
                            sheet.CreateRow(2+r);
                        }
                        if (sheet.GetRow(1 + r) 
                            .GetCell(2 + c) == null) {
                                sheet.GetRow(2 + r).CreateCell(2 + c);
                        }
                        sheet.GetRow(1 + r)
                            .GetCell(2 + c)
                            .SetCellValue(value);
                    }
                }

                hssfworkbook.Write(ms);//
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename=NewAndUpdateDetail" + year + ".xls"));
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();



            }
        }

    }
}